const db=require('../db/index')
const moment=require('moment')

exports.supall=(req,res)=>{
    const sql='select * from supply_view'
    db.query(sql,(err,results)=>{
        if(err) return res.cc(err)
        res.json(results)
    })
}

exports.supfind=(req,res)=>{
    var info=req.body
    var type=info.selecttype
    var sql=` select * from supply_view `
    if(info.inputtext.length!=0){
        var name=info.inputtext
        sql+=` where `
        if(type=='med_name')
        {
            sql+=` med_name like '%${name}%' `
        }else if(type=='sup_name')
        {
            sql+=` sup_name like '%${name}%' `
        }else if(type=='oper_name')
        {
            sql+=` oper_name like '%${name}%' `
        }
    }
    
    if(info.start_date.length!=0||info.end_date.length!=0)
    {
           
    var start_date=moment(info.start_date).format('YYYY-MM-DD HH:mm:ss')
    var end_date=moment(info.end_date).format('YYYY-MM-DD HH:mm:ss')
        if(info.inputtext.length!=0){
            sql+=` and `
        }else if(info.inputtext.length==0)
        {
            sql+=` where `
        }
        if(info.start_date.length!=0&&info.end_date.length!=0){
            sql+=` sup_or_time >= '${start_date}' and sup_or_time <= '${end_date}' `
        }else if(info.start_date.length!=0){
            sql+=` sup_or_time >= '${start_date}' `
        }else if(info.end_date.length!=0){
            sql+=` sup_or_time <= '${end_date}' `
        }
    }
    console.log(sql)
    db.query(sql,(err,results)=>{
        if(err) return res.cc(err)
        res.json(results)
    })
}

exports.supdelete=(req,res)=>{
    const info=req.body
    const sql='delete from supply_order where sup_or_id=?'
    db.query(sql,info.id,(err,results)=>{
        if(err) return res.cc(err)
        if(results.affectedRows!=1) return res.cc('failed to delete')
        res.cc('delete successfully',0)
    })
}
exports.supupdate=(req,res)=>{
    const info=req.body
    const sql='select * from supply_order where sup_or_id=?'
    db.query(sql,info.id,(err,results)=>{
        if(err) return res.cc(err)
        if(results.length!=1) return res.cc('supply order not exist')
        const sql='select * from supplier where sup_id=?'
        db.query(sql,info.sup_id,(err,results)=>{
            if(err) return res.cc(err)
            if(results.length!=1) return res.cc('supplier not exist')
            const sql='select * from medicine where med_id=?'
            db.query(sql,info.med_id,(err,results)=>{
                if(err) return res.cc(err)
                if(results.length!=1) return res.cc('medicine not exist')
                const sql='select * from operator where oper_id=?'
                db.query(sql,info.oper_id,(err,results)=>{
                    if(err) return res.cc(err)
                    if(results.length!=1) return res.cc('operator not exist')
                    const sql='update supply_order set ? where sup_or_id= ?'
                    db.query(sql,[{sup_id:info.sup_id,med_id:info.med_id,med_sup_num:info.num,med_sup_price:info.price,oper_id:info.oper_id},info.id],(err,results)=>{
                        if(err) return res.cc(err)
                        if(results.affectedRows!=1) return res.cc('failed to update')
                        res.cc('update successfully',0)
                    })
                })
            })
        })
    })
}

exports.supinsert=(req,res)=>{
    const info=req.body
    const time =moment().format('YYYY-MM-DD HH:mm:ss');
    const sql='select * from supplier where sup_id=?'
    db.query(sql,info.sup_id,(err,results)=>{
        if(err) return res.cc(err)
        if(results.length!=1) return res.cc('supplier not exist')
        const sql='select * from medicine where med_id=?'
        db.query(sql,info.med_id,(err,results)=>{
            if(err) return res.cc(err)
            if(results.length!=1) return res.cc('medicine not exist')
            const sql='select * from operator where oper_id=?'
            db.query(sql,info.oper_id,(err,results)=>{
                if(err) return res.cc(err)
                if(results.length!=1) return res.cc('operator not exist')
                const sql='insert into supply_order set ? '
                db.query(sql,{sup_id:info.sup_id,med_id:info.med_id,med_sup_num:info.num,med_sup_price:info.price,oper_id:info.oper_id,sup_or_time:time},(err,results)=>{
                    if(err) return res.cc(err)
                    if(results.affectedRows!=1) return res.cc('failed to insert')
                    res.cc('insert successfully',0)
                })
            })
        })
    })
}